from sqlalchemy import and_
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session
from .UserModels import User


# 增
def create_user(name, age, gender, db: Session):
    user = User(name=name, gender=gender, age=age)
    db.add(user)  # 添加到会话
    try:
        db.commit()  # 提交事务
    except IntegrityError:
        db.rollback()  # 回滚事务
        return "用户已存在"
    db.refresh(user)  # 刷新对象
    return user


# 查询全部数据
def get_users_all(db: Session):
    return db.query(User).all()


# 查询单个用户通过用户名
def get_user_by_name(name, db: Session):
    return db.query(User).filter(User.name == name).first()


# 条件查询示例：查询年龄大于某个值的所有用户
def get_users_by_age(age, db: Session):
    data = db.query(User).filter(User.age >= age).all()
    return data


# 复合条件查询：查询年龄大于某个值且用户名包含特定字符串的用户
def get_users_by_age_and_name(age, name, db: Session):
    return db.query(User).filter(
        and_(User.age > age, User.name.contains(name))
    ).all()


# 分页查询
def get_users_by_page(page, db: Session, page_size=20):
    """
    :param page:  页码
    :param page_size:  每页显示数量
    :param db:  数据库会话
    :return:
    """
    return db.query(User).offset((page - 1) * page_size).limit(page_size).all()  # 分页查询，pege-1是因为数据库是从0开始计数


# 更新数据
def update_user(name, age, gender, db: Session):
    user = db.query(User).filter(User.name == name).first()  # 查询用户
    if user:
        user.age = age
        user.gender = gender
        db.commit()  # 提交事务
        return {"status": "success", "msg": "更新成功"}
    else:
        return {"status": "fail", "msg": "用户不存在"}


# 删除数据
def delete_user_by_name(name, db: Session):
    user = db.query(User).filter(User.name == name).first()
    if user:
        db.delete(user)
        db.commit()
        return {"status": "success", "msg": "删除成功"}
    else:
        return {"status": "fail", "msg": "用户不存在"}


# 批量删除数据
# 根据年龄删除数据
def delete_users_by_age(age, db: Session):
    users = db.query(User).filter(User.age > age).all()
    if users:
        # 使用 delete 语句删除所有符合条件的用户
        db.query(User).filter(User.age > age).delete()
        db.commit()
        return {"status": "success", "msg": "删除成功"}
    else:
        return {"status": "fail", "msg": "用户不存在"}
